USE [NANS21V]
GO

/****** オブジェクト:  StoredProcedure [dbo].[ResultEkidenSougou]    スクリプト日付: 11/14/2024 11:30:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ResultEkidenSougou]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ResultEkidenSougou]
GO

CREATE PROCEDURE [dbo].[ResultEkidenSougou]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
	@ROUNDNO int,
	@KUMINO int,
	@DISPLAYFLG int
AS
BEGIN

--競技結果取得
IF @DISPLAYFLG = 1
BEGIN
	DECLARE @KUMISQL nvarchar(max); 
	DECLARE @KUMIPARA nvarchar(max); 
	DECLARE @JYUNIFLG nvarchar(max); 

	SET @KUMISQL = '
		SELECT @JYUNIFLG = KUM.KYOGIFLG
		FROM TBL_KUMI AS KUM

		WHERE KUM.TAIKAINO = '+ STR(@TAIKAINO)+'
		AND KUM.KYOGINO = ' + STR(@KYOGINO) + '
		AND KUM.SUBNO = ' + STR(@SUBNO) + '
		AND KUM.ROUNDNO = ' + STR(@ROUNDNO) + '
		AND KUM.KUMINO = ' + STR(@KUMINO)

	SET @KUMIPARA = '@JYUNIFLG nvarchar(max) OUTPUT';

	EXECUTE sp_executesql @KUMISQL, @KUMIPARA, @JYUNIFLG OUTPUT;



	IF @JYUNIFLG = 7
	BEGIN
		DECLARE @KUKANSQL nvarchar(max); 
		DECLARE @KUKANPARA nvarchar(max); 
		DECLARE @KUKAN nvarchar(max);

		SET @KUKANSQL = '
			SELECT TOP 1  @KUKAN = LAP.ID
			FROM TBL_RESULT_LAP AS RLA

			LEFT JOIN TBL_KYOGI AS KYO
			ON RLA.TAIKAINO = KYO.TAIKAINO 
			AND RLA.KYOGINO = KYO.KYOGINO

			LEFT JOIN TBL_LAP AS LAP
			ON KYO.SYUMOKUNO = LAP.SYUMOKUNO
			AND RLA.LAPNO = LAP.LAPNO

			WHERE RLA.TAIKAINO = '+ STR(@TAIKAINO)+'
			AND RLA.KYOGINO = ' + STR(@KYOGINO) + '
			AND RLA.SUBNO = ' + STR(@SUBNO) + '
			AND RLA.ROUNDNO = ' + STR(@ROUNDNO) + '
			AND RLA.KUMINO = ' + STR(@KUMINO) + '
			AND RLA.KYOGIFLG2 = 1 

			ORDER BY RLA.LAPNO DESC'


			SET @KUKANPARA = '@KUKAN nvarchar(max) OUTPUT';

			EXECUTE sp_executesql @KUKANSQL, @KUKANPARA, @KUKAN OUTPUT;
		
		IF @KUKAN IS NULL BEGIN
			SET @KUKAN = 0
		END
	END

END



DECLARE @SQL2 nvarchar(max); 

IF @DISPLAYFLG = 1
  BEGIN
	SET @SQL2 = '

	SELECT LAN.LANE  '
	IF @JYUNIFLG = 7
	  BEGIN
		SET @SQL2 = @SQL2 + ' ,  RSI.EXTEND4 AS JYUNI
							, '''' as COMMENT'
	  END
	ELSE
	  BEGIN
	    SET @SQL2 = @SQL2 + ' ,  CASE WHEN RES.JYUNI = 0 THEN NULL ELSE RES.JYUNI END AS JYUNI
                            ,  CASE WHEN RES.KIROKU = 0 THEN NULL ELSE RES.KIROKU END AS KIROKU 
							 						  ,  dbo.CommentCodeConvert(RES.RCOMMENT, LAN.SYUKKETU) AS COMMENT '
	  END

	SET @SQL2 = @SQL2 + '

		,  TEM.KYOGISYANO AS TEAMNO
		,  TEM.KYOGISYAMEI AS TEAMMEI
		,  TEM.KYOGISYAKANA AS TEAMKANA
		,  SYO.SYOZOKUMEI AS SYOZOKU
		,  STI.SYOZOKUTIMEI AS SYOZOKUTI
		,  LAN.SYUKKETU
		,  SUBSTRING(KUM.KAISI,1,2) + '':'' + SUBSTRING(KUM.KAISI,3,4)AS KAISI
		,  KUM.ROLLCALLFLG
		,  KUM.KYOGIFLG
	FROM TBL_LANE AS LAN '

	IF @JYUNIFLG = 7
	  BEGIN
		SET @SQL2 = @SQL2 + ' 	LEFT JOIN TBL_RESULT_SIGI AS RSI
								ON RSI.TAIKAINO = LAN.TAIKAINO 
								AND RSI.KYOGINO = LAN.KYOGINO
								AND RSI.SUBNO = LAN.SUBNO   
								AND RSI.ROUNDNO = LAN.ROUNDNO
								AND RSI.KUMINO = LAN.KUMINO 
								AND RSI.LANE = LAN.LANE
								AND RSI.SIGINO = ('+STR(@KUKAN)+'*2)  '
	  END
	ELSE
	  BEGIN
		SET @SQL2 = @SQL2 + ' 	LEFT JOIN TBL_RESULT AS RES 
								ON RES.TAIKAINO = LAN.TAIKAINO 
								AND RES.KYOGINO = LAN.KYOGINO
								AND RES.SUBNO = LAN.SUBNO   
								AND RES.ROUNDNO = LAN.ROUNDNO
								AND RES.KUMINO = LAN.KUMINO 
								AND RES.LANE = LAN.LANE '
	  END
	SET @SQL2 = @SQL2 + '
	LEFT JOIN TBL_KUMI AS KUM   
	ON LAN.TAIKAINO = KUM.TAIKAINO 
	AND LAN.KYOGINO = KUM.KYOGINO

	LEFT JOIN TBL_KYOGISYA AS TEM 
	ON LAN.KYOGISYANO = TEM.KYOGISYANO

	LEFT JOIN TBL_SYOZOKU AS SYO 
	ON TEM.SYOZOKUNO1 = SYO.SYOZOKUNO

	LEFT JOIN TBL_SYOZOKUTI AS STI
	ON SYO.SYOZOKUTINO = STI.SYOZOKUTINO 

	WHERE LAN.TAIKAINO = ' + STR(@TAIKAINO) + ' 
	AND LAN.KYOGINO = ' + STR(@KYOGINO) + '
	AND LAN.SUBNO = ' + STR(@SUBNO) + '       
	AND LAN.ROUNDNO = ' + STR(@ROUNDNO) + ' 
	AND LAN.KUMINO = ' + STR(@KUMINO)

	IF @JYUNIFLG = 7
	  BEGIN
		SET @SQL2 = @SQL2 + ' ORDER BY ISNULL(CAST(RSI.EXTEND4 AS INT),9999) ASC  '
	  END
	ELSE
	  BEGIN
--		SET @SQL2 = @SQL2 + ' ORDER BY ISNULL(RES.JYUNI,9999) ASC  '
      SET @SQL2 = @SQL2 + ' ORDER BY CASE WHEN RES.JYUNI = 0 OR RES.JYUNI IS NULL THEN 9999 ELSE RES.JYUNI END, ISNULL(RES.JYUNI,9999) ASC  '
	  END
  END



ELSE IF @DISPLAYFLG = 0
  BEGIN
	SET @SQL2 = '
		SELECT 
			   LAN.LANE 
      ,  CASE WHEN RES.KIROKU = 0 THEN NULL ELSE RES.KIROKU END AS KIROKU 
      ,  CASE WHEN RES.JYUNI = 0 THEN NULL ELSE RES.JYUNI END AS JYUNI
			,  TEM.KYOGISYANO AS TEAMNO
			,  TEM.KYOGISYAMEI AS TEAMMEI
			,  TEM.KYOGISYAKANA AS TEAMKANA
			,  SYO.SYOZOKUMEI AS SYOZOKU
			,  STI.SYOZOKUTIMEI AS SYOZOKUTI
			,  dbo.CommentCodeConvert(RES.RCOMMENT, LAN.SYUKKETU) AS COMMENT
			,  LAN.SYUKKETU
			,  SUBSTRING(KUM.KAISI,1,2) + '':'' + SUBSTRING(KUM.KAISI,3,4)AS KAISI
			,  KUM.ROLLCALLFLG
			,  KUM.KYOGIFLG
			,  (select max(LAP.ID) from TBL_LAP as LAP where LAP.SYUMOKUNO = KYO.SYUMOKUNO) AS LAP_COUNT
		FROM TBL_LANE AS LAN
		LEFT JOIN TBL_RESULT AS RES ON RES.TAIKAINO = LAN.TAIKAINO AND RES.KYOGINO = LAN.KYOGINO
		  AND RES.SUBNO = LAN.SUBNO   AND RES.ROUNDNO = LAN.ROUNDNO
		  AND RES.KUMINO = LAN.KUMINO AND RES.LANE = LAN.LANE

		LEFT JOIN TBL_KYOGI AS KYO ON LAN.TAIKAINO = KYO.TAIKAINO AND LAN.KYOGINO = KYO.KYOGINO
		LEFT JOIN TBL_KUMI  AS KUM ON LAN.TAIKAINO = KUM.TAIKAINO AND LAN.KYOGINO = KUM.KYOGINO

		LEFT JOIN TBL_KYOGISYA  AS TEM ON LAN.TAIKAINO = TEM.TAIKAINO AND LAN.KYOGISYANO = TEM.KYOGISYANO
		LEFT JOIN TBL_SYOZOKU   AS SYO ON TEM.TAIKAINO = SYO.TAIKAINO AND TEM.SYOZOKUNO1 = SYO.SYOZOKUNO
		LEFT JOIN TBL_SYOZOKUTI AS STI ON SYO.SYOZOKUTINO = STI.SYOZOKUTINO

		WHERE LAN.TAIKAINO = ' + STR(@TAIKAINO) + '
		AND LAN.KYOGINO = ' + STR(@KYOGINO) + '
		AND LAN.SUBNO =  ' + STR(@SUBNO) + '      
		AND LAN.ROUNDNO =  ' + STR(@ROUNDNO) + '
		AND LAN.KUMINO =  ' + STR(@KUMINO) + '

		ORDER BY LAN.LANE ASC ' 
  END

ELSE IF @DISPLAYFLG = 2
  BEGIN
	SET @SQL2 = '
		SELECT 
			   LAN.LANE 
  			,  RES.KIROKU
			,  RES.JYUNI
			,  TEM.KYOGISYANO AS TEAMNO
			,  TEM.KYOGISYAMEI AS TEAMMEI
			,  TEM.KYOGISYAKANA AS TEAMKANA
			,  SYO.SYOZOKUMEI AS SYOZOKU
			,  STI.SYOZOKUTIMEI AS SYOZOKUTI
			,  COM.COMMENTRYAKU AS COMMENT
			  ,  COM.BUNRUI
			,  LAN.SYUKKETU
			,  SUBSTRING(KUM.KAISI,1,2) + '':'' + SUBSTRING(KUM.KAISI,3,4)AS KAISI
			,  KUM.ROLLCALLFLG
			,  KUM.KYOGIFLG
			,  (select max(LAP.ID) from TBL_LAP as LAP where LAP.SYUMOKUNO = KYO.SYUMOKUNO) AS LAP_COUNT
		FROM TBL_LANE AS LAN
		LEFT JOIN TBL_RESULT AS RES ON RES.TAIKAINO = LAN.TAIKAINO AND RES.KYOGINO = LAN.KYOGINO
		  AND RES.SUBNO = LAN.SUBNO   AND RES.ROUNDNO = LAN.ROUNDNO
		  AND RES.KUMINO = LAN.KUMINO AND RES.LANE = LAN.LANE
		LEFT JOIN TBL_COMMENT AS COM ON RES.RCOMMENT = COM.COMMENTNO

		LEFT JOIN TBL_KYOGI AS KYO ON LAN.TAIKAINO = KYO.TAIKAINO AND LAN.KYOGINO = KYO.KYOGINO
		LEFT JOIN TBL_KUMI  AS KUM ON LAN.TAIKAINO = KUM.TAIKAINO AND LAN.KYOGINO = KUM.KYOGINO

		LEFT JOIN TBL_KYOGISYA  AS TEM ON LAN.TAIKAINO = TEM.TAIKAINO AND LAN.KYOGISYANO = TEM.KYOGISYANO
		LEFT JOIN TBL_SYOZOKU   AS SYO ON TEM.TAIKAINO = SYO.TAIKAINO AND TEM.SYOZOKUNO1 = SYO.SYOZOKUNO
		LEFT JOIN TBL_SYOZOKUTI AS STI ON SYO.SYOZOKUTINO = STI.SYOZOKUTINO

		WHERE LAN.TAIKAINO = ' + STR(@TAIKAINO) + '
		AND LAN.KYOGINO = ' + STR(@KYOGINO) + '
		AND LAN.SUBNO =  ' + STR(@SUBNO) + '      
		AND LAN.ROUNDNO =  ' + STR(@ROUNDNO) + '
		AND LAN.KUMINO =  ' + STR(@KUMINO) + '

		ORDER BY TEM.KYOGISYANO ASC ' 
  END
EXECUTE(@SQL2)

END